﻿using MailBox.App_Code.dto;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;

namespace MailBox.App_Code
{
    public enum InboxType : int
    {
        Inbox = 1,
        Sent = 2,
        Spam = 3,
        Drafts = 4,
        Trash = 5
    }
    public class DataUtils
    {
        DbHelper db = new DbHelper();
        public UserLoginDTO GetUserLogin(string userName, string pass)
        {

            pass = Common.GetMd5Hash(pass.Trim());
            UserLoginDTO result = null;
            string sql = "select * From tblClient where userid = (select userid from tbluserlogin where username=@username and password=@password and deleted=0)";
            DataTable table = db.GetTable(sql, new object[] { "username", userName, "password", pass });
            if (table != null && table.Rows.Count > 0)
            {
                result = new UserLoginDTO();
                result.isClient = true;
                ClientDTO client = new ClientDTO();
                client.ClientID = Convert.ToInt32(table.Rows[0]["Clientid"]);
                client.UserID = Convert.ToInt32(table.Rows[0]["Userid"]);
                client.Email = userName;
                client.ClientName = table.Rows[0]["clientname"] + "";
                result.Client = client;
            }
            else
            {
                sql = "select a.*, d.departmentname From tblMB_Accounts a inner join tblMB_Departments d on d.departmentid = a.departmentid where a.isDelete=0 and a.email = @UserName and a.Password = @Password";
                AccountDTO user = (AccountDTO)db.GetObject(new AccountDTO(), sql, new object[] { "UserName", userName, "Password", pass });
                if (user != null && user.AccountID != 0)
                {
                    result = new UserLoginDTO();
                    result.isClient = false;
                    result.Account = user;

                    sql = "select * From tblClient where userid = @userid";
                    table = db.GetTable(sql, new object[] { "userid", user.UserID });
                    if (table != null && table.Rows.Count > 0)
                    {
                        ClientDTO client = new ClientDTO();
                        client.ClientID = Convert.ToInt32(table.Rows[0]["Clientid"]);
                        client.UserID = Convert.ToInt32(table.Rows[0]["Userid"]);
                        client.Email = userName;
                        client.ClientName = table.Rows[0]["clientname"] + "";
                        client.CompanyName = table.Rows[0]["CompanyName"] + "";
                        client.CompanyLogo = table.Rows[0]["CompanyLogo"] + "";
                        result.Client = client;
                    }
                }
            }

            return result;
        }
        public UserLoginDTO GetByUserByEmail(string email)
        {
            UserLoginDTO result = null;
            string sql = "select * From tblClient where userid = (select userid from tbluserlogin where username=@username and deleted=0)";
            DataTable table = db.GetTable(sql, new object[] { "username", email });
            if (table != null && table.Rows.Count > 0)
            {
                result = new UserLoginDTO();
                result.isClient = true;
                ClientDTO client = new ClientDTO();
                client.ClientID = Convert.ToInt32(table.Rows[0]["Clientid"]);
                client.UserID = Convert.ToInt32(table.Rows[0]["Userid"]);
                client.Email = email;
                client.ClientName = table.Rows[0]["clientname"] + "";
                result.Client = client;
            }
            else
            {
                sql = "select * From tblMB_Accounts where isDelete=0 and email = @UserName and Password = @Password";
                AccountDTO user = (AccountDTO)db.GetObject(new AccountDTO(), sql, new object[] { "UserName", email });
                if (user != null && user.AccountID != 0)
                {
                    result = new UserLoginDTO();
                    result.isClient = false;
                    result.Account = user;
                }
            }

            return result;
        }
        public DataTable GetInbox(InboxType type, string email)
        {
            string sql = "SP_MB_GetInbox";
            DataTable table = db.GetTable(sql, new object[] { "InboxType", (int)type, "Email", email });
            return table;
        }
        public DataTable GetInbox(string type, string email, decimal currentid, string mode, int pagecount, string kw)
        {
            string sql = "SP_MB_GetInbox";
            DataTable table = db.GetTable(sql, new object[] { "InboxType", type, "Email", email, "currentid", currentid, "mode", mode, "pagecount", pagecount, "keyword",kw });
            return table;
        }

        internal bool AddInbox(InboxDTO inbox)
        {
            string date = DateTime.Now.ToString("dd/MM/yyyy");
            string sql = "select groupid from tblMB_Groups where isdelete=0 and groupmail=@email";
            object groupid = db.ExecuteScalar(sql, new object[] { "email", inbox.reciever });
            if (groupid + "" != "")
            {
                DataTable revievers = db.GetTable("select email from tblMB_Accounts a inner join tblMB_AccountsInGroup ag on ag.AccountID = a.AccountID where a.isdelete<>1 and ag.groupid = @groupid", new object[] { "groupid", groupid });
                foreach (DataRow row in revievers.Rows)
                {
                    inbox.inbox_reciever = row[0] + "";
                    AddInboxReciever(inbox);
                }
                return true;
            }
            else
            {
                sql = "select count(*) from tblMB_Accounts where isdelete=0 and email=@email";
                object obj = db.ExecuteScalar(sql, new object[] { "email", inbox.reciever });
                int count = 0;
                int.TryParse(obj + "", out count);
                int result = 0;
                if (count > 0)
                {
                    inbox.inbox_reciever = inbox.reciever;
                    return AddInboxReciever(inbox);
                }
            }
            return false;
        }
        FAMailUtils.MailGun.SendMail send = new FAMailUtils.MailGun.SendMail(System.Configuration.ConfigurationManager.AppSettings["api-key"]);
        internal bool AddInboxReciever(InboxDTO inbox)
        {
            int result = 0;
            string sql = "set dateformat dmy insert into tblMB_Inbox(sender, senderName, reciever, inbox_reciever, subject, body, body_html, FileAttached, recieveTime,recieveDate, isopen) values(@sender, @senderName, @reciever, @inbox_reciever, @subject, @body, @body_html, @FileAttached, getdate(),getdate(),0)";
            result = db.ExecuteNoneQuery(sql, new object[]{
                "sender", inbox.sender,
                "senderName", inbox.senderName,
                "reciever", inbox.reciever,
                "inbox_reciever", inbox.inbox_reciever,
                "subject", inbox.subject,
                "body", inbox.body,
                "body_html", inbox.body_html,
                "FileAttached", inbox.FileAttached+""
            });
            string from = inbox.inbox_reciever + "";
            if (from.Trim() != inbox.sender.Trim())
            {
                string auto = this.GetAutoReplyByEmail(from);
                if (!string.IsNullOrWhiteSpace(auto))
                {
                    string domain = from.Split('@')[1];
                    string senderName = this.GetNameByEmail(from);
                    send.SendHtmlMessage(domain, senderName, from, inbox.sender, "", "", "[Trả lời tự động:" + inbox.subject + "]", auto, false, new List<string>());
                }
            }
            return result > 0;
        }


        internal DataTable CheckMail(string email)
        {
            string sql = "[SP_MB_GetInbox_Change]";
            DataTable table = db.GetTable(sql, new object[] { "Email", email });
            return table;
        }

        internal DataTable GetInboxDTO(object emailaccount, string id, string mode)
        {
            string sql = "[SP_MB_GetInboxDTO]";
            DataTable table = db.GetTable(sql, new object[] { "emailaccount", emailaccount, "id", id, "mode", mode });
            return table;
        }

        internal void UpdateOpen(string id)
        {
            string sql = "Update tblMB_Inbox set isOpen = 1, opendate=getdate() where id=@id";
            db.ExecuteNoneQuery(sql, new object[] { "id", id });

        }

        internal string GetNameByEmail(string email)
        {
            string sql = "select accountname From tblMB_Accounts where email=@email";
            object result = db.ExecuteScalar(sql, new object[] { "email", email });
            return result + "";
        }

        internal void AddOutBox(OutboxDTO dto)
        {
            string date = DateTime.Now.ToString("dd/MM/yyyy");
            string sql = "set dateformat dmy insert into tblMB_OutBox(sender, recieverName, reciever, subject, body, SendTime,SendDate, isopen, issent, FileAttached) values(@sender, @recieverName, @reciever, @subject, @body, getdate(),getdate(), 0, @isSent, @FileAttached)";
            int result = db.ExecuteNoneQuery(sql, new object[]{
                "sender", dto.sender,
                "recieverName", dto.recievername,
                "FileAttached", dto.FileAttached,
                "reciever", dto.reciever,
                "subject", dto.subject,
                "body", dto.body,
                "isSent", dto.isSent
            });
            dto.id = result;
        }

        internal void DeleteEmail(string id, string type)
        {
            string sql = @"INSERT INTO [famails].[dbo].[tblMB_Trash]
                                   ([sender]
                                   ,[reciever]
                                   ,[subject]
                                   ,[body]
                                   ,[isOpen]
                                   ,[OpenDate]
                                   ,[RecieveDate]
                                   ,[FileAttached]
                                   ,[MessageId]
                                   ,[CreateTime]
                                   ,[InboxObject]
                                   ,[inbox_reciever], in1_out2_spam3_draft4)
                        select [sender]
                                   ,[reciever]
                                   ,[subject]
                                   ,[body]
                                   ,[isOpen]
                                   ,[OpenDate]
                                   ,[RecieveDate]
                                   ,[FileAttached]
                                   ,[MessageId]
                                   ,GETDATE()
                                   ,senderName
                                   ,[inbox_reciever], 1 from tblMB_InBox where  id=@id
                    
                    delete from tblMB_Inbox where id=@id";
            if (type == "Sent")
            {
                sql = @"INSERT INTO [famails].[dbo].[tblMB_Trash]
                                   ([sender]
                                   ,[reciever]
                                   ,[subject]
                                   ,[body]
                                    ,[FileAttached]
                                    ,[SendDate]
                                    ,[SendTime]
                                    ,[IsOpen]
                                    ,[OpenTime]
                                    ,[recievername]
                                    ,[MessageId]
                                    ,[cc]
                                    ,[bcc]
                                    ,[isSent], in1_out2_spam3_draft4)
                        select [sender]
                              ,[reciever]
                              ,[subject]
                              ,[body]
                              ,[FileAttached]
                              ,[SendDate]
                              ,[SendTime]
                              ,[IsOpen]
                              ,[OpenTime]
                              ,[recievername]
                              ,[MessageId]
                              ,[cc]
                              ,[bcc]
                              ,[isSent], 2 from tblMB_OutBox where  id=@id
                    
                    delete from tblMB_Outbox where id=@id";
            }
            if (type == "Trash")
            {
                sql = "select fileattached from tblMB_Trash where id=@id";
                string path = db.ExecuteScalar(sql, new object[] { "id", id }) + "";
                if (path != "")
                {
                    if (Directory.Exists(path))
                        Directory.Delete(path);
                }
                sql = "delete from tblMB_Trash where id=@id";
            }
            db.ExecuteNoneQuery(sql, new object[] { "id", id });

        }

        internal void SaveSignature(string signature, int isuse, int accountid)
        {
            string sql = "SP_MB_SaveSignature";
            db.ExecuteNoneQuery(sql, new object[] { "signature", signature, "isuse", isuse, "accountid", accountid });
        }
        internal void SaveAutoReply(string AutoReply, int isuse, int accountid)
        {
            string sql = "SP_MB_SaveAutoReply";
            db.ExecuteNoneQuery(sql, new object[] { "AutoReply", AutoReply, "isuse", isuse, "accountid", accountid });
        }

        internal DataTable GetSignatureAndAutoReplyByAccountID(int accountid)
        {
            string sql = @"
                        select signature, isnull(s.isuse_s, 0) isuse_s, autoreply, isnull(s.isuse_r, 0) isuse_r from tblmb_settings  s 
                        where s.accountid=@accountid";

            return db.GetTable(sql, new object[] { "accountid", accountid });
        }
        internal AccountDTO GetAccountByID(int accountid)
        {
            string sql = "select * from tblMB_Accounts where accountid=@accountid";
            return (AccountDTO)db.GetObject(new AccountDTO(), sql, new object[] { "accountid", accountid });
        }

        internal void UpdateAccountInfo(AccountDTO dto)
        {
            string sql = "update tblMB_Accounts set accountname = @accountname, avatar  =@avatar, Phone = @Phone, Address = @Address, BirthDay = @birthday where accountid=@accountid";
            if (dto.BirthDay == null)
            {
                sql = "update tblMB_Accounts set accountname = @accountname, avatar  =@avatar, Phone = @Phone, Address = @Address where accountid=@accountid";
                db.ExecuteNoneQuery(sql, new object[] { "accountid", dto.AccountID, "AccountName", dto.AccountName, "Avatar", dto.Avatar, "Phone", dto.Phone, "Address", dto.Address });
            }
            else db.ExecuteNoneQuery(sql, new object[] { "accountid", dto.AccountID, "AccountName", dto.AccountName, "Avatar", dto.Avatar, "Phone", dto.Phone, "Address", dto.Address, "BirthDay", dto.BirthDay });
        }

        internal string GetSignatureByEmail(string email)
        {
            string sql = "select s.Signature From tblMB_Settings s where s.IsDelete=0 and s.IsUse_S=1 and s.AccountID = (select accountid from tblMB_Accounts a where a.email = @email and a.IsDelete=0)";
            return db.ExecuteScalar(sql, new object[] { "email", email }) + "";
        }

        internal string GetAutoReplyByEmail(string email)
        {
            string sql = "select s.AutoReply From tblMB_Settings s where s.IsDelete=0 and s.IsUse_R=1 and s.AccountID = (select accountid from tblMB_Accounts a where a.email = @email and a.IsDelete=0)";
            return db.ExecuteScalar(sql, new object[] { "email", email }) + "";
        }

        internal DataTable GetAutocompleteByEmail(string email)
        {
            string sql = "SP_MB_GetAutocompleteByEmail";
            return db.GetTable(sql, new object[] { "email", email });
        }

        internal void AccountChangePass(string email, string newpass)
        {
            string sql = "Update tblMB_Accounts set Password=@Password where email=@email and isDelete=0";
            db.ExecuteNoneQuery(sql, new object[] { "email", email, "Password", newpass });
        }
    }
}